package com.oner365.test.generator.sql;

import java.io.File;
import java.io.IOException;
import java.util.List;
import java.util.Objects;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.oner365.test.cast.CastTest;

public class GeneratorSql {

  private static final Logger LOGGER = LoggerFactory.getLogger(CastTest.class);

  public static void main(String[] args) throws IOException {
    List<String> userList = getUserIds();
    userList.stream().forEach(s -> LOGGER.info(s));
    List<String> deviceList = getDeviceInfo();
    deviceList.stream().forEach(s -> LOGGER.info(s));
    List<String> sqlList = generatorDeviceSql(deviceList,userList);
    sqlList.stream().forEach(s -> LOGGER.info(s));
    FileUtils.writeLines(new File("src/main/java/com/oner365/test/generator/sql/new_device.sql"), sqlList);
  }
  
  public static List<String> generatorDeviceSql(List<String> deviceList,List<String> userList) throws IOException {
    String insertSql = "INSERT INTO pfyh.t_device (id,userid,shortudid,udid,idfa,imei,imsi,buytime,warranty,devicetype,type,relationship,devicename,devicenumber,initsiminfo,initsdserialnum,iccid,serialnum,manufacturer,model,os,versionum,phonecode,cpu,ram,romcapacity,romavailablecapacity,sdcapacity,sdavailablecapacity,sdserialnum,camera,wifimac,bluetoothmac,powerstatus,boottime,capacity,capacitystatus,availablecapacity,ip,encryptionlevel,rootflag,lastonlinetime,onlineflag,jailbreakflag,status,registtime,activatetime,token,pushmagic,unlocktoken,challenge,sendregistnoticeflag,allowactrootorjailbreakflag,remark,roamingflag,updatetime,apnstoken,lostflag,dataencryptionflag,specialtypeflag,knoxstatus,productname,offlinestatus,offlinethreshold,emmloginagain,hostid,usagestats,flowupdate,flowquota,clientversion,bitlocker,meid,resolution,oscoreversion,ossoftwareversion,safetyosversion,patchlevel,iccid2,imsi2,networktype,wlanadapterchip,btadapterchip,nfcchip,locatorchip,clientbuildnum,cpuratio,memoryratio,storageratio,bluetoothstate,wifistate,camerastate,microphonestate,mobiledatastate,apn,buildnumber,gpsstate,syncflag,dualstatus,simoper,tpmreport,systemintegrity,idp,idpdeviceid,apptype,subgroupid,orgcode,loginid,maintainflag,screenpwd,want_changed_pwd,label,eraseflag) VALUES ";
    AtomicInteger index = new AtomicInteger(0);
    List<String> sqlList = deviceList.stream().map(value ->{
      StringBuilder sql = new StringBuilder();
      sql.append(insertSql);
      value = value.replace("{userId}", userList.get(index.get()));
      value = value.replaceAll("STR_TO_DATE\\(", "");
      value = value.replaceAll(",'%Y-%m-%d %H:%i:%s'\\)", "");
      sql.append("(");
      sql.append(value);
      sql.append(");");
      index.incrementAndGet();
      if(index.get() >= userList.size()) {
        index.getAndSet(0);
      }
      return sql.toString();
    }).collect(Collectors.toList());
    sqlList.removeIf(Objects::isNull);
    return sqlList;
  }


  public static List<String> getUserIds() throws IOException {
    String filePath = "src/main/java/com/oner365/test/generator/sql/user_rel.sql";
    File file = new File(filePath);
    List<String> list = FileUtils.readLines(file, "UTF-8");
    List<String> userList = list.stream().map(s -> {
      String str = StringUtils.substringBetween(s, "VALUES(", ");");
      if (str != null) {
        String[] data = str.split(",");
        return data[0];
      }
      return null;
    }).collect(Collectors.toList());
    userList.removeIf(Objects::isNull);
    return userList;
  }

  public static List<String> getDeviceInfo() throws IOException {
    String filePath = "src/main/java/com/oner365/test/generator/sql/device_old.sql";
    File file = new File(filePath);
    AtomicInteger index = new AtomicInteger(90000000);
    List<String> list = FileUtils.readLines(file, "UTF-8");
    String groupFilePath = "src/main/java/com/oner365/test/generator/sql/groupid.txt";
    File groupFile = new File(groupFilePath);
    List<String> groupIdList = FileUtils.readLines(groupFile, "UTF-8");
    AtomicInteger indexGroup = new AtomicInteger(0);
    List<String> deviceList = list.stream().map(s -> {
      if (!s.contains("INSERT INTO") && !";".equals(s) && s != null) {
        int start = 1;
        if (s.startsWith(",")) {
          start = 2;
        }
        String str = StringUtils.substring(s, start, s.lastIndexOf(")"));
        String[] data = str.split(",");
        data[0] = ""+index.incrementAndGet();
        data[1] = "{userId}";
        data[data.length-8] = groupIdList.get(indexGroup.get());
        indexGroup.incrementAndGet();
        if(indexGroup.get() >= groupIdList.size()) {
          indexGroup.getAndSet(0);
        }
        StringBuilder device = new StringBuilder();
        for (String d : data) {
          device.append(d + ",");
        }
        return device.substring(0, device.toString().length() - 1);
      }
      return null;
    }).collect(Collectors.toList());
    deviceList.removeIf(Objects::isNull);
    return deviceList;
  }

}
